﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using DBUtility;
using MySql.Data.MySqlClient;

namespace DAL
{
    public class DCallRecords
    {

        /// <summary>
        /// 增加一条数据
        /// </summary>
        public bool Add(Model.CallRecordsInfo model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into tcallrecords(");
            strSql.Append("phoneNumber,calltime,type)"); 
            strSql.Append(" values (");
            strSql.Append("@phoneNumber,@calltime,@type)");
            MySqlParameter[] parameters = {
					new MySqlParameter("@phonenumber", MySqlDbType.VarChar,20),					
					new MySqlParameter("@calltime", MySqlDbType.VarChar,45),
                    new MySqlParameter("@type", MySqlDbType.VarChar,2)};
            parameters[0].Value = model.PhoneNumber;
            parameters[1].Value = model.CallTime;
            parameters[2].Value = model.Type;     
            int rows = DbHelperMySQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        /// 获得数据列表
        /// </summary>
        public DataSet GetList(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select  Id,phoneNumber,");
            strSql.Append("(case type when '1' then '去电' when '0' then '来电' end)");
            //strSql.Append("(case type when '1' then '平台拨出' when '0' then '平台拨入' ");
            //strSql.Append("when '3' then '出租车被叫' when '4' then '出租车主叫' end)");
            strSql.Append("as type,calltime FROM tcallrecords where (type='0' or type ='1')");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" and " + strWhere);
            }
            strSql.Append(" order by Id desc");
            return DbHelperMySQL.Query(strSql.ToString());
        }

        public int GetCount(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(*) from tcallrecords ");
            if (strWhere.Trim() != "")
            {
                strSql.Append("where");
                strSql.Append(strWhere);                
            }
            strSql.Append(" order by Id desc");
            DataSet ds = DbHelperMySQL.Query(strSql.ToString());
            if (ds == null || ds.Tables.Count != 1 || ds.Tables[0].Rows.Count != 1)
            {
                return -1;
            }
            return int.Parse(ds.Tables[0].Rows[0][0].ToString());
        }
         
        public DataSet GetReportByDay(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(case type when '1' then type end) as platOut,");//
            strSql.Append("count(case type when '3' then type end) as DrCallIn,");//
            strSql.Append("DATE_FORMAT(calltime, '%m-%d' ) AS DT ");                 
            strSql.Append("From tcallrecords ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            strSql.Append(" group by DT order by DT asc");
            return DbHelperMySQL.Query(strSql.ToString());
        }

        public DataSet GetListByPage(int page, int pagesize, string strWhere)
        {
            StringBuilder strSql = new StringBuilder();            
            strSql.Append("select  Id,phoneNumber,");
            strSql.Append("(case type when '1' then '平台拨出' when '0' then '平台拨入' ");
            strSql.Append("when '3' then '出租车被叫' when '4' then '出租车主叫' end)");
            strSql.Append("as type,calltime FROM tcallrecords ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            strSql.Append(" order by Id desc");
            strSql.Append("limit ");
            strSql.Append((page - 1) * pagesize);
            strSql.Append(",");
            strSql.Append(pagesize);
            DataSet ds = DbHelperMySQL.Query(strSql.ToString());
            return ds;

        }
    }
}
